-- ====================================
-- 我们的小宇宙 - 数据库表结构设计
-- Database: PostgreSQL 14+
-- ====================================

-- 启用UUID扩展（用于生成唯一ID）
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 1. 情侣关系表 (couples)
-- 作用：存储情侣关系的基础信息，整个应用的根基表
CREATE TABLE couples (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    partner1_name VARCHAR(50) NOT NULL,                    -- 伴侣1姓名
    partner2_name VARCHAR(50) NOT NULL,                    -- 伴侣2姓名
    relationship_start_date DATE NOT NULL,                 -- 恋爱开始日期
    anniversary_name VARCHAR(100) DEFAULT '纪念日',        -- 纪念日名称
    couple_avatar_emoji VARCHAR(10) DEFAULT '👩‍❤️‍👨',      -- 情侣头像表情
    relationship_status VARCHAR(20) DEFAULT 'active',      -- 关系状态：active, paused, ended
    timezone VARCHAR(50) DEFAULT 'Asia/Shanghai',          -- 时区设置
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 2. 用户表 (users)  
-- 作用：管理情侣应用中的用户信息，每个情侣关系包含两个用户
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    couple_id UUID NOT NULL REFERENCES couples(id) ON DELETE CASCADE,
    name VARCHAR(50) NOT NULL,                             -- 用户姓名
    avatar_emoji VARCHAR(10) DEFAULT '👩',                 -- 用户头像表情
    role VARCHAR(10) NOT NULL CHECK (role IN ('partner1', 'partner2')), -- 角色标识
    email VARCHAR(255),                                    -- 邮箱（可选）
    phone VARCHAR(20),                                     -- 手机号（可选）
    birthday DATE,                                         -- 生日
    bio TEXT,                                              -- 个人简介
    preferences JSONB DEFAULT '{}',                        -- 用户偏好设置（JSON格式）
    last_active_at TIMESTAMP WITH TIME ZONE,              -- 最后活跃时间
    is_active BOOLEAN DEFAULT true,                        -- 是否活跃
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- 确保每个情侣关系只有两个用户，且角色唯一
    UNIQUE(couple_id, role)
);

-- 3. 回忆表 (memories)
-- 作用：存储详细的回忆记录，支持富文本内容、多媒体和标签
CREATE TABLE memories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,                          -- 回忆标题
    description TEXT,                                      -- 简要描述
    content TEXT,                                          -- 详细内容
    memory_date DATE NOT NULL,                             -- 回忆发生的日期
    location_name VARCHAR(200),                            -- 地点名称
    location_coordinates POINT,                            -- 地理坐标
    image_url TEXT,                                        -- 主图片URL
    image_paths JSONB DEFAULT '[]',                        -- 图片路径数组
    image_count INTEGER DEFAULT 0,                         -- 图片数量
    mood_type VARCHAR(20),                                 -- 心情类型
    tags JSONB DEFAULT '[]',                               -- 标签数组
    is_favorite BOOLEAN DEFAULT false,                     -- 是否收藏
    is_private BOOLEAN DEFAULT false,                      -- 是否私密（仅自己可见）
    view_count INTEGER DEFAULT 0,                          -- 查看次数
    weather_snapshot_id UUID,                              -- 关联天气快照
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 4. 时光轴事件表 (timeline_events)
-- 作用：记录时间线上的重要事件，相对简洁，专注时间线展示
CREATE TABLE timeline_events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,                          -- 事件标题
    description TEXT,                                      -- 事件描述
    event_date DATE NOT NULL,                              -- 事件日期
    category VARCHAR(50) NOT NULL,                         -- 事件分类：约会、旅行、庆祝、里程碑等
    emoji VARCHAR(10) NOT NULL DEFAULT '📝',               -- 事件图标
    image_paths JSONB DEFAULT '[]',                        -- 图片路径数组
    location_name VARCHAR(200),                            -- 地点名称
    importance_level INTEGER DEFAULT 3 CHECK (importance_level BETWEEN 1 AND 5), -- 重要程度1-5
    is_milestone BOOLEAN DEFAULT false,                    -- 是否为里程碑事件
    weather_snapshot_id UUID,                              -- 关联天气快照
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 5. 重要日子表 (important_days)
-- 作用：管理需要提醒的特殊日期，支持重复提醒和读取状态
CREATE TABLE important_days (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,                          -- 重要日子标题
    description TEXT,                                      -- 详细描述
    target_date DATE NOT NULL,                             -- 目标日期
    category VARCHAR(50) NOT NULL,                         -- 分类：纪念日、生日、节日、约定等
    emoji VARCHAR(10) NOT NULL DEFAULT '⭐',               -- 图标表情
    is_recurring BOOLEAN DEFAULT false,                    -- 是否每年重复
    recurring_type VARCHAR(20) DEFAULT 'yearly',          -- 重复类型：yearly, monthly, weekly
    reminder_days_before INTEGER[] DEFAULT ARRAY[1,7,30], -- 提前提醒天数数组
    note TEXT,                                             -- 备注信息
    is_read BOOLEAN DEFAULT false,                         -- 是否已读
    read_at TIMESTAMP WITH TIME ZONE,                      -- 标记已读的时间
    read_by_user_id UUID REFERENCES users(id),            -- 谁标记的已读
    priority INTEGER DEFAULT 3 CHECK (priority BETWEEN 1 AND 5), -- 优先级1-5
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 6. 每日心情表 (daily_moods)  
-- 作用：记录用户每日的心情状态，支持心情同步和背景变化
CREATE TABLE daily_moods (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    mood_type VARCHAR(20) NOT NULL,                        -- 心情类型：sunny, cloudy, rainy等
    mood_date DATE NOT NULL,                               -- 心情日期
    mood_note TEXT,                                        -- 心情备注
    mood_intensity INTEGER DEFAULT 5 CHECK (mood_intensity BETWEEN 1 AND 10), -- 心情强度1-10
    shared_with_partner BOOLEAN DEFAULT true,              -- 是否与伴侣分享
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- 确保每个用户每天只有一条心情记录
    UNIQUE(user_id, mood_date)
);

-- 7. 情话表 (love_quotes)
-- 作用：存储每日情话内容，为应用提供温馨的文案支持
CREATE TABLE love_quotes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    content TEXT NOT NULL,                                 -- 情话内容
    category VARCHAR(50) DEFAULT 'general',                -- 分类：morning, evening, anniversary等
    language VARCHAR(10) DEFAULT 'zh-CN',                  -- 语言
    mood_tags VARCHAR(50)[] DEFAULT '{}',                  -- 适用心情标签
    usage_count INTEGER DEFAULT 0,                         -- 使用次数
    is_active BOOLEAN DEFAULT true,                        -- 是否启用
    created_by VARCHAR(50) DEFAULT 'system',               -- 创建者
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 8. 天气设置表 (weather_settings)
-- 作用：存储用户的天气偏好和位置信息
CREATE TABLE weather_settings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    city_name VARCHAR(100) NOT NULL,                       -- 城市名称
    city_code VARCHAR(50),                                 -- 城市代码
    latitude DECIMAL(10, 7),                               -- 纬度
    longitude DECIMAL(10, 7),                              -- 经度
    auto_location BOOLEAN DEFAULT true,                    -- 是否自动定位
    weather_provider VARCHAR(50) DEFAULT 'qweather',      -- 天气数据提供商
    api_key_encrypted TEXT,                                -- 加密的API密钥
    show_air_quality BOOLEAN DEFAULT true,                 -- 是否显示空气质量
    show_humidity BOOLEAN DEFAULT true,                    -- 是否显示湿度
    show_wind BOOLEAN DEFAULT true,                        -- 是否显示风力
    temperature_unit VARCHAR(10) DEFAULT 'celsius',       -- 温度单位：celsius, fahrenheit
    update_frequency INTEGER DEFAULT 3600,                -- 更新频率（秒）
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- 确保每个用户只有一个天气设置
    UNIQUE(user_id)
);

-- 9. 天气缓存表 (weather_cache)  
-- 作用：缓存最新的天气数据，减少API调用，提高响应速度
CREATE TABLE weather_cache (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    city_code VARCHAR(50) NOT NULL,                        -- 城市代码
    temperature DECIMAL(4, 1),                             -- 当前温度
    temperature_min DECIMAL(4, 1),                         -- 最低温度
    temperature_max DECIMAL(4, 1),                         -- 最高温度
    weather_code VARCHAR(20),                              -- 天气代码
    weather_icon VARCHAR(10),                              -- 天气图标
    weather_description VARCHAR(100),                      -- 天气描述
    humidity INTEGER,                                      -- 湿度百分比
    wind_speed DECIMAL(4, 1),                              -- 风速
    wind_direction VARCHAR(10),                            -- 风向
    air_quality_index INTEGER,                             -- 空气质量指数
    air_quality_level VARCHAR(20),                         -- 空气质量等级
    visibility DECIMAL(4, 1),                              -- 能见度
    uv_index INTEGER,                                      -- 紫外线指数
    weather_time TIMESTAMP WITH TIME ZONE,                 -- 天气数据时间
    cached_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),      -- 缓存时间
    expires_at TIMESTAMP WITH TIME ZONE,                   -- 过期时间
    
    -- 确保每个用户每个城市只有一条最新缓存
    UNIQUE(user_id, city_code)
);

-- 10. 天气快照表 (weather_snapshots)
-- 作用：为回忆和时光轴事件保存创建时的天气状况，增强回忆的情感价值
CREATE TABLE weather_snapshots (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    memory_id UUID REFERENCES memories(id) ON DELETE SET NULL,      -- 关联的回忆ID
    event_id UUID REFERENCES timeline_events(id) ON DELETE SET NULL, -- 关联的事件ID
    city_name VARCHAR(100),                                -- 城市名称
    temperature DECIMAL(4, 1),                             -- 当时温度
    weather_icon VARCHAR(10),                              -- 天气图标
    weather_description VARCHAR(100),                      -- 天气描述
    humidity INTEGER,                                      -- 湿度
    air_quality_level VARCHAR(20),                         -- 空气质量
    wind_description VARCHAR(50),                          -- 风力描述
    snapshot_date DATE NOT NULL,                           -- 快照日期
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- 确保至少关联一个对象（回忆或事件）
    CHECK (memory_id IS NOT NULL OR event_id IS NOT NULL)
);

-- 11. 系统配置表 (system_configs)
-- 作用：存储应用全局配置，如版本信息、功能开关等
CREATE TABLE system_configs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    config_key VARCHAR(100) NOT NULL UNIQUE,              -- 配置键
    config_value JSONB,                                    -- 配置值（JSON格式）
    description TEXT,                                      -- 配置描述
    is_public BOOLEAN DEFAULT false,                       -- 是否公开（客户端可访问）
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 12. 用户会话表 (user_sessions)
-- 作用：管理用户登录会话，支持多设备登录
CREATE TABLE user_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    session_token VARCHAR(255) NOT NULL UNIQUE,           -- 会话令牌
    device_info JSONB DEFAULT '{}',                        -- 设备信息
    ip_address INET,                                       -- IP地址
    user_agent TEXT,                                       -- 用户代理
    is_active BOOLEAN DEFAULT true,                        -- 是否活跃
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,          -- 过期时间
    last_used_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),   -- 最后使用时间
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ====================================
-- 索引设计 (提升查询性能)
-- ====================================

-- 用户相关索引
CREATE INDEX idx_users_couple_id ON users(couple_id);
CREATE INDEX idx_users_role ON users(couple_id, role);
CREATE INDEX idx_users_last_active ON users(last_active_at);

-- 回忆相关索引  
CREATE INDEX idx_memories_user_id ON memories(user_id);
CREATE INDEX idx_memories_date ON memories(memory_date DESC);
CREATE INDEX idx_memories_user_date ON memories(user_id, memory_date DESC);
CREATE INDEX idx_memories_tags ON memories USING GIN(tags);
CREATE INDEX idx_memories_favorite ON memories(user_id, is_favorite) WHERE is_favorite = true;

-- 时光轴事件索引
CREATE INDEX idx_timeline_events_user_id ON timeline_events(user_id);
CREATE INDEX idx_timeline_events_date ON timeline_events(event_date DESC);
CREATE INDEX idx_timeline_events_category ON timeline_events(category);
CREATE INDEX idx_timeline_events_importance ON timeline_events(importance_level DESC);

-- 重要日子索引
CREATE INDEX idx_important_days_user_id ON important_days(user_id);
CREATE INDEX idx_important_days_date ON important_days(target_date);
CREATE INDEX idx_important_days_recurring ON important_days(is_recurring, target_date);
CREATE INDEX idx_important_days_unread ON important_days(user_id, is_read) WHERE is_read = false;

-- 心情记录索引
CREATE INDEX idx_daily_moods_user_date ON daily_moods(user_id, mood_date DESC);
CREATE INDEX idx_daily_moods_date ON daily_moods(mood_date DESC);

-- 天气缓存索引
CREATE INDEX idx_weather_cache_user_city ON weather_cache(user_id, city_code);
CREATE INDEX idx_weather_cache_expires ON weather_cache(expires_at);

-- 用户会话索引
CREATE INDEX idx_user_sessions_token ON user_sessions(session_token);
CREATE INDEX idx_user_sessions_user_id ON user_sessions(user_id);
CREATE INDEX idx_user_sessions_expires ON user_sessions(expires_at);

-- ====================================
-- 触发器和函数 (自动化数据维护)
-- ====================================

-- 自动更新 updated_at 字段的函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为需要的表创建 updated_at 触发器
CREATE TRIGGER update_couples_updated_at BEFORE UPDATE ON couples FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();
CREATE TRIGGER update_memories_updated_at BEFORE UPDATE ON memories FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();
CREATE TRIGGER update_timeline_events_updated_at BEFORE UPDATE ON timeline_events FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();
CREATE TRIGGER update_important_days_updated_at BEFORE UPDATE ON important_days FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();
CREATE TRIGGER update_daily_moods_updated_at BEFORE UPDATE ON daily_moods FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();
CREATE TRIGGER update_weather_settings_updated_at BEFORE UPDATE ON weather_settings FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();
CREATE TRIGGER update_system_configs_updated_at BEFORE UPDATE ON system_configs FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();

-- 清理过期天气缓存的函数
CREATE OR REPLACE FUNCTION clean_expired_weather_cache()
RETURNS void AS $$
BEGIN
    DELETE FROM weather_cache WHERE expires_at < NOW();
END;
$$ LANGUAGE plpgsql;

-- 清理过期会话的函数  
CREATE OR REPLACE FUNCTION clean_expired_sessions()
RETURNS void AS $$
BEGIN
    DELETE FROM user_sessions WHERE expires_at < NOW();
END;
$$ LANGUAGE plpgsql;

-- ====================================
-- 初始数据插入
-- ====================================

-- 插入默认系统配置
INSERT INTO system_configs (config_key, config_value, description, is_public) VALUES
('app_version', '"1.0.0"', '应用版本号', true),
('maintenance_mode', 'false', '维护模式开关', true),
('max_daily_memories', '10', '每日最大回忆创建数量', false),
('weather_cache_duration', '3600', '天气缓存时长（秒）', false),
('session_duration', '2592000', '会话有效期（秒，默认30天）', false),
('supported_languages', '["zh-CN", "en-US"]', '支持的语言列表', true);

-- 插入默认情话数据
INSERT INTO love_quotes (content, category, language, mood_tags) VALUES
('愿我们的爱情永远甜蜜如初 💕', 'general', 'zh-CN', ARRAY['happy', 'love']),
('每一天和你在一起，都是最美的时光 ✨', 'morning', 'zh-CN', ARRAY['sunny', 'grateful']),
('晚安，我的宝贝，愿你有个甜美的梦 🌙', 'evening', 'zh-CN', ARRAY['love', 'thoughtful']),
('感谢命运让我遇见了你，我生命中最美好的存在 🙏', 'anniversary', 'zh-CN', ARRAY['grateful', 'love']),
('无论天气如何变化，你都是我心中永远的阳光 ☀️', 'general', 'zh-CN', ARRAY['sunny', 'heart']),
('下雨天也不怕，因为有你在身边 🌧️', 'rainy', 'zh-CN', ARRAY['rainy', 'love']);

-- ====================================
-- 数据库优化建议
-- ====================================

-- 设置数据库连接池参数（根据实际情况调整）
-- max_connections = 100
-- shared_buffers = 256MB  
-- effective_cache_size = 1GB
-- work_mem = 4MB
-- maintenance_work_mem = 64MB

-- 启用统计信息收集
-- ALTER SYSTEM SET track_activities = on;
-- ALTER SYSTEM SET track_counts = on;
-- ALTER SYSTEM SET track_io_timing = on;

-- 设置自动清理参数
-- ALTER SYSTEM SET autovacuum = on;
-- ALTER SYSTEM SET autovacuum_max_workers = 3;

COMMENT ON DATABASE our_little_universe IS '我们的小宇宙 - 情侣生活记录应用数据库';
